package org.boot.easyapi.core.dao;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.boot.easyapi.core.dao.dto.param.AndParamDTO;
import org.boot.easyapi.core.dao.dto.param.RequestSqlParamsDTO;
import org.boot.easyapi.core.entity.ExcelConfigDO;
import org.boot.easyapi.core.framework.jdbc.JdbcRepository;
import org.boot.easyapi.core.util.sql.MappingConvertUtil;
import org.boot.easyapi.properties.EasyApiProperties;
import org.boot.easyapi.core.util.QueryParser;
import org.boot.easyapi.core.util.sql.DefaultSqlOperationUtil;
import org.boot.easyapi.core.dao.dto.sql.SqlSelectLastMapDTO;
import org.boot.easyapi.core.util.excelconfig.ExcelConfigUtil;
import org.boot.easyapi.core.util.sql.ParamsOperaBuildUtil;
import org.boot.easyapi.pojo.auto.AuthenticationUserInfo;
import org.boot.easyapi.util.CollUtil;
import org.boot.easyapi.util.ObjectUtil;
import org.boot.easyapi.util.excel.ExcelExporterUtil;
import org.boot.easyapi.util.excel.ExcelImportUtil;
import org.boot.easyapi.util.web.AuthenticationUtil;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;
import static org.boot.easyapi.core.service.table.impl.TableServiceImpl.defaultFields;
import static org.boot.easyapi.core.util.RespKeyUtil.checkRespKey;
import static org.boot.easyapi.core.util.RespKeyUtil.getRespKey;
import static org.boot.easyapi.core.util.sql.ParamsOperaBuildUtil.*;
import static org.boot.easyapi.exception.BusinessException.exception;

@Slf4j
@Repository
public class JdbcOperaRepository {

    @Resource(name = "easyapi-jdbcRepository")
    private JdbcRepository jdbcRepository;

    @Resource
    private DefaultSqlOperationUtil defaultSqlOperationUtil;

    @Resource
    private EasyApiProperties properties;

    @Resource(name = "easyapi-excelConfigRepository")
    private ExcelConfigRepository excelConfigRepository;


    /**
     * 单条插入
     * @param table 表名
     * @param colValMap 列名-值映射
     * @return 主键值
     */
    public Long insert(String table, Map<String, Object> colValMap) {
        Assert.notEmpty(colValMap, "colValMap 不能为空");
        Map<String, Object> finalColValMap = defaultSqlOperationUtil.insertDefaultColValMap(colValMap);
        String sql = buildInsertSql(table, finalColValMap.keySet());
        return jdbcRepository.insert(sql, finalColValMap.values().toArray());
    }

    /* ---------- 2. 批量插入 ---------- */
    public int[] insertBatch(String table, List<Map<String, Object>> list) {
        Assert.notEmpty(list, "list 不能为空");

        List<Map<String, Object>> finalColValMapList = defaultSqlOperationUtil.insertDefaultColValMap(list);

        Set<String> cols = finalColValMapList.get(0).keySet();
        String sql = buildInsertSql(table, cols);
        List<Object[]> args = finalColValMapList.stream()
                .map(m -> cols.stream().map(m::get).toArray())
                .collect(Collectors.toList());
        return jdbcRepository.batchUpdate(sql, args);
    }

    /* ---------- 3. 单条更新（按主键） ---------- */
    public int update(String table, Map<String, Object> colValMap) {
        Object idObj = colValMap.get("id");
        if (ObjectUtil.isEmpty(idObj)) throw exception("id 不能为空");

        Long id = Long.parseLong(idObj.toString());
        colValMap = defaultSqlOperationUtil.updateDefaultColValMap(colValMap);
        String sql = buildUpdateSql(table, colValMap.keySet());
        List<Object> args = new ArrayList<>();
        colValMap.forEach((k, v) -> args.add(v));
        args.add(id);
        return jdbcRepository.update(sql, args.toArray());
    }

    /* ---------- 4. 批量更新（按主键） ---------- */
    public List<Integer> updateBatch(String table, List<Map<String, Object>> list) {
        List<Integer> updateCounts = new ArrayList<>();
        for (Map<String, Object> map : list) {
            updateCounts.add(update(table, map));
        }
        return updateCounts;
    }

    /* ---------- 5. 删除 ---------- */
    public int delete(String table, Long id) {
        String sql = defaultSqlOperationUtil.defaultDeleted(table) + " WHERE id = ?";
        return jdbcRepository.update(sql, id);
    }

    public int[] deleteBatch(String table, List<Long> ids) {
        String sql = defaultSqlOperationUtil.defaultDeleted(table) + " WHERE id in (?)";
        List<Object[]> args = ids.stream().map(i -> new Object[]{i}).collect(Collectors.toList());
        return jdbcRepository.batchUpdate(sql, args);
    }

    /* ---------- 6. 查询 ---------- */
    /** 根据主键查一条，返回 Map */
    public Map<String, Object> getById(String table, Object id, String respKey) {
        SqlSelectLastMapDTO build = QueryParser.build(new RequestSqlParamsDTO(null, null,
                buildDefaultAndParam()), true);
        StringBuilder sql = new StringBuilder(" SELECT ")
                .append(buildSelectFields(table, respKey))
                .append(" FROM ")
                .append(table)
                .append( " WHERE 1=1 ")
                .append(build.getSql())
                .append(" AND  id = ? LIMIT 1 ");
        List<Object> args = new ArrayList<>(Arrays.asList(build.getArgs()));
        args.add(id);
        return jdbcRepository.queryForMap(sql.toString(), args.toArray());
    }

    /** 条件列表查询 */
    public List<Map<String, Object>> list(String table, Map<String, Object> param) {
        if(EasyApiProperties.properties().getApi().isOnlyCamelCase()){
            param = MappingConvertUtil.mappingFiled(param);
        }
        //解析入参map，追加默认查询
        RequestSqlParamsDTO parse = QueryParser.parse(table, param);
        parse.getWhereParam().addAll(buildDefaultAndParam());
        //构建入参sql
        SqlSelectLastMapDTO build = QueryParser.build(parse, false);


        StringBuilder sql = new StringBuilder(" SELECT ")
                .append(buildSelectFields(table, param))
                .append(" FROM ")
                .append(table)
                .append( " WHERE 1=1 ").append(build.getSql());
        return jdbcRepository.queryForList(sql.toString(), build.getArgs());
    }

    /** 分页查询 */
    public List<Map<String, Object>> page(String table, Map<String, Object> param) {
        if(EasyApiProperties.properties().getApi().isOnlyCamelCase()){
            param = MappingConvertUtil.mappingFiled(param);
        }
        //解析入参map，追加默认查询
        RequestSqlParamsDTO parse = QueryParser.parse(table, param);
        parse.getWhereParam().addAll(buildDefaultAndParam());
        //构建入参sql
        SqlSelectLastMapDTO build = QueryParser.build(parse, true);
        StringBuilder sql = new StringBuilder(" SELECT ")
                .append(buildSelectFields(table, param))
                .append(" FROM ")
                .append(table)
                .append( " WHERE 1=1 ").append(build.getSql());
        return jdbcRepository.queryForList(sql.toString(), build.getArgs());
    }

    /** 统计查询 */
    public Long count(String table, Map<String, Object> param) {
        if(EasyApiProperties.properties().getApi().isOnlyCamelCase()){
            param = MappingConvertUtil.mappingFiled(param);
        }
        //解析入参map，构建入参sql
        SqlSelectLastMapDTO build = QueryParser.build(QueryParser.parse(table, param), false);
        StringBuilder sql = new StringBuilder(" SELECT ").append(" count(*) ").append(" FROM ").append(table)
                .append(" WHERE 1=1 ").append(build.getSql());
        return jdbcRepository.queryForObject(sql.toString(), Long.class, build.getArgs());
    }


    /**
     * 导出excel
     * @param table
     * @param param
     * @return
     */
    public byte[] exportExcel(String table, Long configId, Map<String, Object> param){
        if(EasyApiProperties.properties().getApi().isOnlyCamelCase()){
            param = MappingConvertUtil.mappingFiled(param);
        }
        RequestSqlParamsDTO parse = QueryParser.parse(table, param);
        parse.getWhereParam().addAll(buildDefaultAndParam());
        SqlSelectLastMapDTO build = QueryParser.build(parse, false);

        StringBuilder sql = new StringBuilder(" SELECT ");

        ExcelConfigDO excelConfigDO = null;
        if(ObjectUtil.isNotEmpty(configId)) excelConfigDO = excelConfigRepository.findOne(configId);
        if(excelConfigDO != null){
            Map<String, Object> configJson = JSON.parseObject(excelConfigDO.getConfigJson(), Map.class);
            sql.append(buildExcelFields(table, param, configJson));
        }else{
            sql.append(buildExcelFields(table, param));
        }
        sql.append(" FROM ")
                .append(table)
                .append( " WHERE 1=1 ").append(build.getSql());
        List<Map<String, Object>> list = jdbcRepository.queryForList(sql.toString(), build.getArgs());
        if (CollUtil.isEmpty(list)) throw exception("导出数据为空");
        return ExcelExporterUtil.exportExcel(list);
    }

    /**
     * 导入excel
     * @param table
     * @param configId
     * @param file
     * @return
     */
    public String importExcel(String table, Long configId, MultipartFile file){
        ExcelConfigDO excelConfigDO = excelConfigRepository.findOne(configId);
        checkExcelImport(table, excelConfigDO);

        //解析配置json，并转为 map
        Map<String, Object> configJson = JSON.parseObject(excelConfigDO.getConfigJson(), Map.class);

        //校验json配置规则
        if(ExcelConfigUtil.checkExcelConfigJsonExpression(table, configJson)){
            //解析excel文件
            List<Map<String, Object>> list = ExcelImportUtil.analysisExcel(file, configJson);
            //批量插入数据
            this.insertBatch(table, removeDefaultFields(list));
            return "导入成功！共导入" + list.size() + "条数据";
        }
        return "导入失败！";
    }

    private void checkExcelImport(String tableName, ExcelConfigDO excelConfigDO){
        // 1. 校验文件类型
        if (excelConfigDO == null) {
            throw new IllegalArgumentException("excel配置不存在");
        }
        if (!excelConfigDO.getTableName().equals(tableName)) {
            throw new IllegalArgumentException("该配置不适配当前excel!");
        }
    }
    //去除默认字段
    private List<Map<String, Object>> removeDefaultFields(List<Map<String, Object>> list){
        list.forEach(map -> map.keySet().removeIf(key -> defaultFields.containsKey(key.toLowerCase())));
        return list;
    }



    /* ========================== 内部 SQL 构建 ========================== */
    private String buildSelectFields(String table, Map<String, Object> param) {
        String respKey = getRespKey(param);
        //检查是否为强制VO模式
        checkRespKey(properties.getApi().isOnlyResponseVo(), respKey);
        //解析查询字段
        String fields = ParamsOperaBuildUtil.getFieldsNotOnlyCamelCase(table, respKey);
        if(properties.getApi().isOnlyCamelCase()) fields = ParamsOperaBuildUtil.getFields(table, respKey);
        return fields;
    }
    private String buildSelectFields(String table, String respKey) {
        //检查是否为强制VO模式
        checkRespKey(properties.getApi().isOnlyResponseVo(), respKey);
        //解析查询字段
        String fields = ParamsOperaBuildUtil.getFieldsNotOnlyCamelCase(table, respKey);
        if(properties.getApi().isOnlyCamelCase()) fields = ParamsOperaBuildUtil.getFields(table, respKey);
        return fields;
    }
    private String buildExcelFields(String table, Map<String, Object> param) {
        String respKey = getRespKey(param);
        //检查是否为强制VO模式
        checkRespKey(properties.getApi().isOnlyResponseVo(), respKey);
        //解析查询字段
        return convertExcelAsFields(table, respKey);
    }
    private String buildExcelFields(String table, Map<String, Object> param, Map<String, Object> configJson) {
        String respKey = getRespKey(param);
        //检查是否为强制VO模式
        checkRespKey(properties.getApi().isOnlyResponseVo(), respKey);
        //解析查询字段
        return convertExcelAsFields(table, respKey, configJson);
    }
    private List<AndParamDTO> buildDefaultAndParam() {
        List<AndParamDTO> AndParamDTO = new ArrayList<>();
        //检查是否开启逻辑删除
        if (properties.getTable().getApp().isDeletedLogic()){
            AndParamDTO.add(new AndParamDTO("deleted", "EQ", 0));
        }
        //检查是否开启多租户
        if (properties.getApi().isTenantEnable()){
            AuthenticationUserInfo userInfo = AuthenticationUtil.getAuthenticationUserInfo();
            if(ObjectUtil.isNotEmpty(userInfo.getTenantId())){
                AndParamDTO.add(new AndParamDTO("tenant_id", "EQ", userInfo.getTenantId()));
            }else{
                log.error("当前用户未指定租户！");
            }
        }
        return AndParamDTO;
    }

    private String buildInsertSql(String table, Set<String> cols) {
        String columns = String.join(",", cols);
        String placeholders = cols.stream().map(c -> "?").collect(Collectors.joining(","));
        return "INSERT INTO " + table + " (" + columns + ") VALUES (" + placeholders + ")";
    }

    private String buildUpdateSql(String table, Set<String> cols) {
        String set = cols.stream()
                .map(c -> c + " = ?")
                .collect(Collectors.joining(","));
        return "UPDATE " + table + " SET " + set + " WHERE id = ?";
    }
}